﻿using BaseUtils.Logger;
using DatabaseCore.Modal;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;

namespace DatabaseCore.Sqlite
{
    public class SqliteHelper : DataSourceClient
    {
        // 默认数据库索引 id 为 0；
        public static readonly int SystemDefaultDBID = 0;
        // private readonly string SystemDefaultDB = $@"data source={AppDomain.CurrentDomain.BaseDirectory}projects\\App.db;version=3;synchronous=normal;journal_mode=WAL;";

        private SqlSugarClient db;
        public bool Connection;
        private static object DBLock = new object();

        public SqliteHelper()
        {
        }


        // 提供静态创建方法，确保连接被初始化
        public static SqliteHelper Create()
        {
            var helper = new SqliteHelper();
            helper.ConnectionSqlite(); // 强制执行连接
            return helper;
        }

        private readonly string SystemDefaultDB = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "projects", "App.db");
        // 连接字符串单独处理
        private string GetConnectionString(string dbPath)
        {
            // 简化连接字符串，先排除可能引起问题的参数
            //return $"data source={dbPath};version=3;";
            return $"data source={dbPath};version=3;connect timeout=60;default command timeout=60;";
        }
        //public SqliteHelper(DBConfig dBConfig)
        //{
        //    dBConfig.DbType = "Sqlite";
        //    db = GetOpenConnection(dBConfig);
        //}

        public SqliteHelper ConnectionSqlite(string connectionString = null)
        {
            if (db != null && Connection)
            {
                return this; // 已连接则直接返回
            }

            try
            {
                // 路径处理逻辑（保持不变）
                if (string.IsNullOrEmpty(connectionString))
                {
                    string dbDir = Path.GetDirectoryName(SystemDefaultDB);
                    if (!Directory.Exists(dbDir)) Directory.CreateDirectory(dbDir);
                    connectionString = GetConnectionString(SystemDefaultDB);
                }

                db = OpenConnection(connectionString, DbType.Sqlite);
                db.Ado.Open(); // 测试连接
                db.Ado.Close();
                Connection = true;
            }
            catch (Exception ex)
            {
                Connection = false;
                db = null; // 连接失败则重置
                LoggerService.Error($"连接失败：{ex.Message}", ex);
            }
            return this;
        }
        // 所有数据库操作前检查 db 是否有效
        private void CheckDbValid()
        {
            if (db == null || !Connection)
            {
                throw new InvalidOperationException("数据库连接未初始化或已失效，请先调用ConnectionSqlite()");
            }
            // 新增：检查连接是否已关闭（SqlSugar 提供的连接状态判断）
            if (!db.Ado.IsValidConnection())
            {
                string error = "数据库连接已失效或关闭，尝试重新连接...";
                LoggerService.Error(error);

                // 自动重试连接（避免手动处理）
                try
                {
                    ConnectionSqlite(); // 重新执行连接逻辑
                    if (!Connection)
                        throw new InvalidOperationException("重新连接失败");
                }
                catch (Exception ex)
                {
                    LoggerService.Error("重新连接数据库失败：" + ex.Message, ex);
                    throw; // 抛出异常，避免继续执行
                }
            }

            // 原有校验：Connection 标记是否有效
            if (!Connection)
            {
                string error = "数据库连接未建立，请先调用 ConnectionSqlite()";
                LoggerService.Error(error);
                throw new InvalidOperationException(error);
            }
        }
        public SqliteHelper ConnectionMysql(string ConnectionString = null)
        {
            db = OpenConnection(ConnectionString, DbType.MySql);
            Connection = db.IsAnyConnection(string.Empty);
            return this;
        }

        /// <summary>
        /// 查找数据表表名
        /// </summary>
        /// <returns></returns>
        public List<SystemTable> FindDatabaseTableNames()
        {
            string sql = "SELECT name FROM sqlite_master WHERE type='table';";
            List<SystemTable> SystemTables = db.Ado.SqlQuery<SystemTable>(sql);
            List<SystemFieldMapping> Mapping = db.Queryable<SystemFieldMapping>().GroupBy(s => s.TableName).ToList();
            foreach (SystemTable item in SystemTables)
            {
                foreach (SystemFieldMapping sym in Mapping)
                {
                    if (string.IsNullOrEmpty(item.TableAlias))
                    {
                        item.TableAlias = item.name;
                    }
                    if (sym.TableName == item.name)
                    {
                        item.TableAlias = sym.TableAlias;
                        item.TableName = item.name;
                    }
                }
            }
            return SystemTables;
        }

        /// <summary>
        /// 获取表结构和列别名
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<TableStructure> FindTableStructures(string tableName)
        {
            lock (DBLock)
            {
                // 2. 手动实现SQLite表名转义：单引号替换为双单引号（核心修复）
                CheckDbValid();
                string sql = $"SELECT * FROM pragma_table_info(@tableName);";
                Console.WriteLine($"执行查询前连接状态：{db.Ado.IsValidConnection()}");
                List<TableStructure> TableStructures = db.Ado.SqlQuery<TableStructure>(sql, new SugarParameter("@tableName", tableName));
                Console.WriteLine($"执行查询后连接状态：{db.Ado.IsValidConnection()}");
                List<SystemFieldMapping> Mapping = Query<SystemFieldMapping>(m => m.TableName == tableName);
                foreach (TableStructure item in TableStructures)
                {
                    item.Column = item.name;
                    item.Alias = item.name;
                    foreach (SystemFieldMapping sym in Mapping)
                    {

                        if (sym.FieldName == item.name)
                        {
                            item.Alias = sym.FieldAlias;
                            item.MappingId = sym.Id;
                            break;
                        }
                    }

                }
                return TableStructures;
            }
        }

        /// <summary>
        /// 根据sql语句查询
        /// </summary>
        /// <param name="Sql"></param>
        /// <returns></returns>
        public DBResult QueryBySql(string Sql)
        {
            List<object> TableStructures = db.Ado.SqlQuery<object>(Sql);
            DBResult result = new DBResult(TableStructures);
            result.TotalCount = TableStructures.Count;
            return result;
        }

        public DBResult PageQueryBySql(string Sql, string TableName, int Page, int Size)
        {
            //Sql += $" limit {Page},{Size}";
            DBResult Result = QueryBySql(Sql);
            string[] _Sql = Regex.Split(Sql, "limit");
            string CountSql = _Sql[0].Replace("SELECT * FROM", "SELECT Count(*) FROM ") + ";";

            int Count = db.Ado.SqlQuery<int>(CountSql).FirstOrDefault();
            return new DBResult(Result.Data, Count, Page, Size);
        }

        /// <summary>
        /// 插入、更新和删除
        /// 
        /// 返回：影响的行数
        /// </summary>
        /// <param name="Sql"></param>
        /// <returns></returns>
        public int ExecuteSql(string Sql)
        {
            int res = db.Ado.ExecuteCommand(Sql);
            return res;
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> QueryAll<T>() where T : class, new()
        {
            List<T> Reset = default(List<T>);
            lock (DBLock)
            {
                try
                {
                    // 检查db是否初始化
                    if (db == null)
                    {
                        LoggerService.Error("QueryAll失败：数据库连接未初始化，请先调用ConnectionSqlite()");
                        return Reset;
                    }
                    if (!Connection)
                    {
                        LoggerService.Error("QueryAll失败：数据库连接未建立");
                        return Reset;
                    }
                    Reset = db.Queryable<T>().ToList();
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"查询失败，原因：{ex.Message}", ex);
                }
            }
            return Reset;
        }

        /// <summary>
        /// 根据条件查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public List<T> Query<T>(System.Linq.Expressions.Expression<Func<T, bool>> whereExpression) where T : class, new()
        {
            CheckDbValid();
            lock (DBLock)
            {
                try
                {
                    return db.Queryable<T>().Where(whereExpression).ToList();
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"查询失败：{ex.Message}", ex);
                    return new List<T>();
                }
            }
        }

        public T QueryOne<T>(System.Linq.Expressions.Expression<Func<T, bool>> whereExpression) where T : class, new()
        {
            return db.Queryable<T>().Where(whereExpression).First();
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Insert<T>(T entity) where T : class, new()
        {
            return db.Insertable(entity).ExecuteCommand() > 0;
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Delete<T>(T entity) where T : class, new()
        {
            return db.Deleteable(entity).ExecuteCommand() > 0;
        }

        public string Query(string pt, string stationName, string materialName)
        {
            throw new NotImplementedException();
        }

        public T Install<T>(T t) where T : class, new()
        {
            return db.Insertable(t).IgnoreColumns(ignoreNullColumn: true).ExecuteReturnEntity();
        }

        int DataSourceClient.Update<T>(T t)
        {
            return db.Updateable(t).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
        }

        int DataSourceClient.Update<T>(List<T> t)
        {
            return db.Updateable(t).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
        }

        //private readonly string Datasource = AppDomain.CurrentDomain.BaseDirectory + "App.db";

        public SqlSugarClient OpenConnection(string ConnectionString, DbType type)
        {
            SqlSugarClient SSC = new SqlSugarClient(new ConnectionConfig()
            {
                DbType = type,
                ConnectionString = ConnectionString,
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute // 明确主键配置
            });
            //调式 打印SQL 
            SSC.Aop.OnLogExecuting = (sql, pars) =>
            {
                LoggerService.Info(sql + "\r\n" +
                    SSC.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value)));
            };
            Connection = SSC.IsAnyConnection(string.Empty);
            return SSC;
        }
    }
}
